package com.ddone.demo;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.data.CommentData;
import com.alibaba.excel.metadata.data.HyperlinkData;
import com.alibaba.excel.metadata.data.RichTextStringData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.ddone.demo.bean.Teacher;
import com.ddone.demo.bean.User;
import com.ddone.demo.config.CustomCellWriteHeightConfig;
import com.ddone.demo.config.CustomCellWriteWidthConfig;
import com.ddone.demo.util.EasyExcelUtil;
import net.datafaker.Faker;
import org.apache.commons.lang3.RandomUtils;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.junit.Before;
import org.junit.Test;

import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.concurrent.TimeUnit;

/**
 * @author ddone
 * @date 2024/4/12-17:05
 */

public class EasyExcelWriteTest {

    private Faker faker;

    @Before
    public void init() {
        faker = new Faker(Locale.CHINA);
    }

    @Test
    public void testBasicWrite() {
        //xls: 老版本的excel文件一次性最多可以写入65535行数据
        //xlsx: 新版本的excel文件 写入数据一般没有限制  去掉了默认的样式 文件比老版本小
        String workPath = System.getProperty("user.dir");
        String filePath = workPath + "/src/main/resources/excel/t1";


        //目标文件
        EasyExcel
                // .write(filePath+ ExcelTypeEnum.XLS.getValue())
                .write(filePath + ExcelTypeEnum.XLSX.getValue())
                // excel中的头：以后通过excel映射的bean的属性名解析
                .head(Teacher.class)
                // excel中的一个工作簿
                .sheet("teachers")
                // 写入到excel中的数据集合
                .doWrite(data());
    }

    @Test
    public void testWriteAutoSize() {
        String workPath = System.getProperty("user.dir");
        String filePath = workPath + "/src/main/resources/excel/t2";
        EasyExcel.write(filePath + ExcelTypeEnum.XLSX.getValue())
                .head(Teacher.class)
                .sheet("teachers")
                .registerWriteHandler(new CustomCellWriteWidthConfig())
                .registerWriteHandler(new CustomCellWriteHeightConfig())
                .registerWriteHandler(EasyExcelUtil.getStyleStrategy())
                .doWrite(data());
    }

    private List<Teacher> data() {
        ArrayList<Teacher> teachers = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            Teacher teacher = Teacher.builder()
                    .id(i)
                    .name(faker.name().name())
                    .age(faker.number().numberBetween(22, 60))
                    .salary(faker.number().randomDouble(2, 30000, 40000))
                    .comment(faker.text().text())
                    .joinDate(faker.date().past(500, TimeUnit.DAYS))
                    .link(faker.internet().url())
                    .build();
            teachers.add(teacher);
        }
        return teachers;
    }

    @Test
    public void testLinkComment() {
        List<User> data = new ArrayList<>();
        User user = new User();
        user.setId(10);
        //需求：给一个文本设置一个超链接，点击excel中的文本时可以跳转到链接地址
        WriteCellData<String> link = new WriteCellData<>("百度");
        //文本内容
        //设置链接内容：
        HyperlinkData hyperLinkData = new HyperlinkData();
        hyperLinkData.setAddress("http://www.baidu.com");
        //链接地址
        hyperLinkData.setHyperlinkType(HyperlinkData.HyperlinkType.URL);
        //链接类型
        link.setHyperlinkData(hyperLinkData);
        //设置单元格样式
        WriteCellStyle writeCellStyle = new WriteCellStyle();
        writeCellStyle.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex());
        writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        //link.setRichTextStringDataValue();  设置渐变色
        link.setWriteCellStyle(writeCellStyle);
        user.setLink(link);
        //需求：给文本添加备注信息
        WriteCellData<String> comment = new WriteCellData<>("备注");
        CommentData commentData = new CommentData();
        commentData.setAuthor("ddone");
        commentData.setRichTextStringData(new RichTextStringData("测试用户的备注信息"));
        comment.setCommentData(commentData);
        user.setComment(comment);

        data.add(user);
        // 写入数据
        String workPath = System.getProperty("user.dir");
        String filePath = workPath + "/src/main/resources/excel/t2";
        EasyExcel.
                write(filePath + ExcelTypeEnum.XLSX.getValue())
                .head(User.class)
                .sheet(1)
                .doWrite(data);
    }

    /**
     * 测试追加写入
     */
    @Test
    public void testAppend() {
        // 1. 判断 如果要写入的文件不存在, 直接写入
        // 2. 如果写入的数据文件已存在,使用已存在的文件当中模板和本次要写入的数据一起写入到一个临时文件中
        // 3. 删除原来的数据文件 修改临时文件名为原来文件的名称
        String workPath = System.getProperty("user.dir");
        String filePath = workPath + "/src/main/resources/excel/t3.xlsx";
        String tempPath = filePath.split("\\.")[0] + "_temp.xlsx";
        File file = new File(filePath);
        File tempFile = new File(tempPath);
        if (!file.exists()) {
            EasyExcel.write(file)
                    .head(Teacher.class)
                    .sheet("abc")
                    .doWrite(data());
        } else {
            EasyExcel.write(tempFile)
                    .withTemplate(file)
                    .inMemory(true)
                    .sheet("abc")
                    .needHead(false)
                    .doWrite(data());
        }
        if (tempFile.exists()) {
            file.delete();
            tempFile.renameTo(file);
        }
    }

    @Test
    public void testNewSheet(){

        // 1. 判断 如果要写入的文件不存在, 直接写入
        // 2. 如果写入的数据文件已存在,使用已存在的文件当中模板和本次要写入的数据一起写入到一个临时文件中
        // 3. 删除原来的数据文件 修改临时文件名为原来文件的名称
        String workPath = System.getProperty("user.dir");
        String filePath = workPath + "/src/main/resources/excel/t4.xlsx";
        String tempPath = filePath.split("\\.")[0] + "_temp.xlsx";
        File file = new File(filePath);
        File tempFile = new File(tempPath);
        if (!file.exists()) {
            EasyExcel.write(file)
                    .head(Teacher.class)
                    .sheet("1")
                    .doWrite(data());
        } else {
            EasyExcel.write(tempFile)
                    .withTemplate(file)
                    .inMemory(true)
                    .sheet("2")
                    .needHead(true)
                    .head(Teacher.class)
                    .doWrite(data());
        }
        if (tempFile.exists()) {
            file.delete();
            tempFile.renameTo(file);
        }
    }

}
